# -*- codeing = utf-8 -*-
# @Time : 2022/11/9 19:46
# @Author : 小马
# @File: Methods.py
# @Software : PyCharm

import sqlite3
import xlrd
import xlwt
#从月度总水量表中获得数据并将数据插入到数据表WaterConsumption中
def CreateWaterConsumption():
    dbpath = "water.db"
    sql = '''
            create table WaterConsumption
            (
            id integer primary key autoincrement,
            date text,
            Machine1 text,
            Machine2 text,
            Machine3 text,
            Machine4 text,
            Machine5 text,
            Machine6 text,
            totaltime text,
            totalwater text,
            totalconsumption text
            )
            '''
    conn = sqlite3.connect(dbpath)
    cur = conn.cursor()
    cur.execute(sql)

    conn.commit()
    cur.close()
    conn.close()
def searchall():
    datalist = []
    con = sqlite3.connect("water.db")
    cur = con.cursor()
    sql = "select date,Machine1,Machine2,Machine3,Machine4,Machine5,Machine6,totaltime from WaterConsumption"
    data = cur.execute(sql)
    for item in data:
        datalist.append(item)

    cur.close()
    con.close()
    return datalist
def searchMachine():
    m1=[]
    m2=[]
    m3=[]
    m4=[]
    m5=[]
    m6=[]
    con = sqlite3.connect("water.db")
    cur = con.cursor()
    sql1 = "select Machine1,Machine2,Machine3,Machine4,Machine5,Machine6 from WaterConsumption"
    data1 = cur.execute(sql1)
    for item in data1:
        m1.append(item[0])
        m2.append(item[1])
        m3.append(item[2])
        m4.append(item[3])
        m5.append(item[4])
        m6.append(item[5])
    cur.close()
    con.close()
    return m1,m2,m3,m4,m5,m6
def searchDateAndWater():
    date=[]
    wate=[]
    con = sqlite3.connect("water.db")
    cur = con.cursor()
    sql = "select date,totaltime from WaterConsumption"
    data = cur.execute(sql)
    for item in data:
        date.append(item[0])
        wate.append(item[1])


    cur.close()
    con.close()
    return date,wate

def ReadExcel():

    conn=sqlite3.connect("water.db")
    cur=conn.cursor()
    data=xlrd.open_workbook("月度总用水量.xls")
    sheet=data.sheet_by_name("Sheet1")
    for i in range(1,sheet.nrows):
        sql = '''insert into WaterConsumption(date,Machine1,Machine2,Machine3,Machine4,Machine5,Machine6,totalconsumption,totaltime,totalwater)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''%(sheet.row(i)[0].value, sheet.row(i)[1].value, sheet.row(i)[2].value,sheet.row(i)[3].value,sheet.row(i)[4].value,sheet.row(i)[5].value,sheet.row(i)[6].value,sheet.row(i)[7].value,sheet.row(i)[8].value,sheet.row(i)[9].value)
        cur.execute(sql)
        conn.commit()
    cur.close()
    conn.close()


    #创建WaterConsumption水量消耗表格

#将注册手机号,邮箱,密码插入到数据库中
def InsertIntoTable(p,pa,e):
    sql = '''insert into UserTable(phonenumber,password,email,isdeleted)values(%s,%s,%s,0)'''%(p,pa,e)
    conn=sqlite3.connect("water.db")
    cur=conn.cursor()
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()
#根据手机号查询密码和逻辑删除来实现登陆功能
def SelectFromTable(p):
    sql='''select password from UserTable where phonenumber=%s and isdeleted=0'''%p
    conn=sqlite3.connect("water.db")
    cur=conn.cursor()
    pa=cur.execute(sql)
    result=pa.fetchone()
    conn.commit()
    cur.close()
    conn.close()
    return result[0]

#创建数据库并建表
def CreateTableAndDataBase():
    dbpath="water.db"
    sql='''
    create table UserTable
    (
    id integer primary key autoincrement,
    phonenumber text,
    password text,
    email text,
    isdeleted integer
    )
    '''
    conn=sqlite3.connect(dbpath)
    cur=conn.cursor()
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()
def deleteTable():
    dbpath = "water.db"
    sql = '''
            drop table WaterConsumption
            '''
    conn = sqlite3.connect(dbpath)
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()
#将数据写入excel表格
def exportTo():

    data = xlrd.open_workbook("月度总用水量.xls")
    sheet = data.sheet_by_name("Sheet1")
    # #for i in range(1, sheet.nrows):


    workbook = xlwt.Workbook(encoding="UTF-8")  # 创建workbook对象   一个文件
    worksheet = workbook.add_sheet('sheet1')

    for i in range(0,10):
        worksheet.write(0,i,sheet.row(0)[i].value)
    for i in range(1, sheet.nrows):
        for j in range(0, 10):
           worksheet.write(i, j, sheet.row(i)[j].value)
    workbook.save('C:/Users/DELL/Downloads/月度总用水表.xls')
def searchSumSix():
    datalist=[]
    sql="select sum(Machine1),sum(Machine2),sum(Machine3),sum(Machine4),sum(Machine5),sum(Machine6) from WaterConsumption"
    dbpath = "water.db"
    conn = sqlite3.connect(dbpath)
    cur = conn.cursor()
    data=cur.execute(sql)
    for item in data:
      datalist.append(item)
    conn.commit()
    cur.close()
    conn.close()
    return datalist

if __name__=='__main__':
    d=searchSumSix()
    print(int(d[0][0]))
    print(d[0][1])
